package wjx.database.view;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.swing.Box;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;

import wjx.database.bean.JTableUtils;
import wjx.database.jdbc.JDBCUtils;

public class QueryFrm extends JFrame {
	private int num;

	private JPanel contentPane;
	private JLabel label1, label2, label;
	private JTextField text1, text2, text3;
	private Box box, box1, box2, box3, box4, box5, box6;
	private JButton button1, button2, button3;
	private JComboBox combo;
	private JTable table1, table2;
	private JScrollPane scroll1, scroll2;

	private String[] listName;
	private String[] qQuery;
	private String[] type;
	private String title;
	private String sql;
	private String order;
	private String[][] data = null;
	private ResultSet rs;
	private String code = "";

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
//					QueryFrm frame = new QueryFrm("", 1);
//					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public QueryFrm(String name, String[] queryStr, String[] queryType, int isAll) {
		code = JDBCUtils.GetTableRemarks(name);
		System.out.println("cde = "+code);
					
		setTitle("查询-表'" + name + "'");
		qQuery = new String[queryStr.length];
		qQuery = queryStr;
		type = new String[queryType.length];
		type = queryType;
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setResizable(false);
		setBounds(100, 100, 700, 600);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		contentPane.setLayout(new BorderLayout(0, 0));
		setContentPane(contentPane);

		button1 = new JButton("查询");
		button1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				queryActionPerformed(e);
			}
		});
		button2 = new JButton("退出");
		button2.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				quitActionPerformed(e);
			}
		});
		button3 = new JButton("重置");
		button3.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				clearActionPerformed(e);
			}
		});

		listName = new String[queryStr.length];
		num = queryStr.length;
		for (int i = 0; i < num; i++) {
			if( code.equals(qQuery[i])) {
				listName[i] = qQuery[i] + '(' + type[i] + "[加密列])";
				continue ;
			}
			listName[i] = qQuery[i] + '(' + type[i] + ')';
		}
		

		if (isAll == 1) {
			// 获取表的所有列名
			sql = "select * from databaseManager." + name;

			// 加载数据
			try {
				rs = JDBCUtils.query(sql);
				// 加载data
				rs.last();
				data = new String[rs.getRow()][queryStr.length];
				rs = JDBCUtils.query(sql);
				int cnt = 0;
				while (rs.next()) {
					for (int j = 0; j < queryStr.length; j++) {
						data[cnt][j] = rs.getString(queryStr[j]);	
						if(code.equals(qQuery[j])) {
							MainFrm.myt.getEn(data[cnt][j]);
							synchronized(MainFrm.myt){
								MainFrm.myt.wait();
							}
							data[cnt][j] += '(' + MainFrm.myt.res + ')';
						}
//						System.out.println(data[cnt][j]);
					}
					cnt++;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		} else {
			// 仅输出部分列名
			sql = "select ";
			for (int i = 0; i < queryStr.length; i++) {
				if (i > 0)
					sql += ",";
				sql += queryStr[i];
			}
			sql += " from databaseManager." + name;
			System.out.println(sql);

			// 加载data
			try {
				rs = JDBCUtils.query(sql);
				rs.last();
				data = new String[rs.getRow()][queryStr.length];
				rs = JDBCUtils.query(sql);
				int cnt = 0;
				while (rs.next()) {
					for (int j = 0; j < queryStr.length; j++) {
						data[cnt][j] = rs.getString(queryStr[j]);
						if(code.equals(qQuery[j])) {
							MainFrm.myt.getEn(data[cnt][j]);
							synchronized(MainFrm.myt){
								MainFrm.myt.wait();
							}
							data[cnt][j] += '(' + MainFrm.myt.res + ')';
						}
//						System.out.println(data[cnt][j]);
					}
					cnt++;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		// 输入查找条件
		label = new JLabel("请输入查询条件(支持<、>、=)");
		label.setFont(new Font("Lucida Grande", 1, 15));
		title = "select ";
		for (int i = 0; i < num; i++) {
			if (i > 0)
				title += ",";
			title += queryStr[i];
		}
		title += " from databaseManager." + name;
		label1 = new JLabel(title);
		label2 = new JLabel(" where ");

		table1 = new JTable(1, num);
		DefaultTableModel model = (DefaultTableModel) table1.getModel();
		model.setColumnIdentifiers(listName);
		table1.setModel(model);
		scroll1 = new JScrollPane(table1);
		scroll1.setPreferredSize(new Dimension(700,65));
		JTableUtils.setTable(table1);
		
		combo = new JComboBox();
		combo.setModel(new DefaultComboBoxModel(queryStr));
		combo.setSelectedIndex(-1);
		combo.addItemListener(new ItemListener() {
			@Override
			public void itemStateChanged(ItemEvent e) {
				selectOrderAction(combo.getSelectedItem().toString());
			}
		});

		// 根据查找条件显示结果
		table2 = new JTable();
		DefaultTableModel modelQ = new DefaultTableModel(data, listName) {
			public boolean isCellEditable(int row, int column) {
				return false;
			}
		};
		table2.setModel(modelQ);
//		table2.setPreferredScrollableViewportSize(new Dimension(num*100, 500));
		JTableUtils.setTable(table2);
//		DefaultTableModel model_ = (DefaultTableModel) table2.getModel();
//		model_.setColumnIdentifiers(listName);

		scroll2 = new JScrollPane(table2);
		

		// 箱式布局
		box1 = Box.createHorizontalBox();
		box1.add(label);
		box2 = Box.createHorizontalBox();
		box2.add(label1);
		box2.add(label2);

		box5 = Box.createHorizontalBox();
		box5.add(scroll1);
		
		Box boxOr = Box.createHorizontalBox();
		boxOr.add(new JLabel("ORDER BY"));
		boxOr.add(combo);

		box3 = Box.createHorizontalBox();
		box3.add(button1);
		box3.add(button3);
		box3.add(button2);

		box6 = Box.createHorizontalBox();
		box6.add(new JLabel("查询结果"));

		box4 = Box.createHorizontalBox();
		box4.add(scroll2);

		box = Box.createVerticalBox();
		box.add(Box.createVerticalStrut(10));
		box.add(box1);
		box.add(Box.createVerticalStrut(10));
		box.add(box2);
		box.add(Box.createVerticalStrut(10));
		box.add(box5);
		box.add(boxOr);
		box.add(box3);
		box.add(box6);
		box.add(Box.createVerticalStrut(3));
		box.add(box4);

		contentPane.add(box);
		setContentPane(contentPane);
//		pack();
		this.setLocationRelativeTo(null);
	}

	private void selectOrderAction(String string) {
		// 实现order by条件
		order = " order by " + combo.getSelectedItem().toString();
		// 构建查询语句的前半部分
		sql = title;
		String s;
		int cnt = 0;
		for (int i = 0; i < num; i++) {
			s = (String) table1.getValueAt(0, i);
			System.out.println("$value" + s);
			if (s == null || s.length() == 0)
				continue;
			if (cnt == 0)
				sql += " where ";
			if (cnt > 0)
				sql += " AND ";
			sql += qQuery[i] + s;
			cnt++;
		}
		// 获取查询条件
		System.out.println("order :" + sql + order);

		try {
			rs = JDBCUtils.query(sql+order);
			rs.last();
			data = new String[rs.getRow()][qQuery.length];
			rs = JDBCUtils.query(sql+order);
			int cnt_ = 0;
			while (rs.next()) {
				for (int j = 0; j < qQuery.length; j++) {
					data[cnt_][j] = rs.getString(qQuery[j]);
					System.out.println(data[cnt_][j]);
				}
				cnt_++;
			}
		} catch (SQLException e1) {
			JOptionPane.showMessageDialog(null, "语法错误！请检查您的输入！");
		}
		// 显示查询结果
		table2 = new JTable();
		DefaultTableModel modelQ = new DefaultTableModel(data, listName) {
			public boolean isCellEditable(int row, int column) {
				return false;
			}
		};
		table2.setModel(modelQ);
//		table2.setPreferredScrollableViewportSize(new Dimension(num*100, 500));
		JTableUtils.setTable(table2);

		box.remove(box4);
		scroll2 = new JScrollPane(table2);

		box4.removeAll();
		box4.add(scroll2);

		box.add(box4);
		contentPane.add(box);
		remove(contentPane);

		setContentPane(contentPane);
//		pack();

	}

	private void clearActionPerformed(ActionEvent e) {
		for (int i = 0; i < num; i++) {
			table1.setValueAt("", 0, i);
		}
	}

	private void queryActionPerformed(ActionEvent e) {
		if( combo.getSelectedIndex() == -1 )
			order = "" ;
		else
			order = " order by " + combo.getSelectedItem().toString();
		
		// TODO 这里需要考虑数据类型实现条件查询 由用户自行确认
		sql = title;
		String s;
		int cnt = 0;
		for (int i = 0; i < num; i++) {
			s = (String) table1.getValueAt(0, i);
			System.out.println("$value" + s);
			if (s == null || s.length() == 0)
				continue;
			if (cnt == 0)
				sql += " where ";
			if (cnt > 0)
				sql += " AND ";
			sql += qQuery[i] + s;
			cnt++;
		}
		// 获取查询条件
		System.out.println("query :" + sql);

		try {
			rs = JDBCUtils.query(sql+order);
			rs.last();
			data = new String[rs.getRow()][qQuery.length];
			rs = JDBCUtils.query(sql+order);
			int cnt_ = 0;
			while (rs.next()) {
				for (int j = 0; j < qQuery.length; j++) {
					data[cnt_][j] = rs.getString(qQuery[j]);
					if(code.equals(qQuery[j])) {
						MainFrm.myt.getEn(data[cnt_][j]);
						synchronized(MainFrm.myt){
							MainFrm.myt.wait();
						}
						data[cnt_][j] += '(' + MainFrm.myt.res + ')';
					}
					System.out.println(data[cnt_][j]);
				}
				cnt_++;
			}
		} catch (Exception e1) {
			JOptionPane.showMessageDialog(null, "语法错误！请检查您的输入！");
		}
		// 显示查询结果
		table2 = new JTable();
		DefaultTableModel modelQ = new DefaultTableModel(data, listName) {
			public boolean isCellEditable(int row, int column) {
				return false;
			}
		};
		table2.setModel(modelQ);
//		table2.setPreferredScrollableViewportSize(new Dimension(num * 100, 500));
		JTableUtils.setTable(table2);

		box.remove(box4);
		scroll2 = new JScrollPane(table2);

		box4.removeAll();
		box4.add(scroll2);

		box.add(box4);
		contentPane.add(box);
		remove(contentPane);

		setContentPane(contentPane);
//		pack();
	}

	private void quitActionPerformed(ActionEvent e) {
		// 退出
		int result = JOptionPane.showConfirmDialog(null, "是否退出查询？", "退出查询", JOptionPane.YES_NO_OPTION);
		// 退出窗口
		if (result == JOptionPane.YES_OPTION)
			dispose();
		return;
	}

}
